﻿

CREATE PROCEDURE [dbo].[dsc_GenDecl205]
	@an int,
	@totBC decimal(15,0) OUT,
	@totI decimal(15,0) OUT,
	@nrBen int OUT
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    SET @totBC = 0
    SET  @totI = 0
	SET @nrBen = 0



	select @nrBen=COUNT(Distinct a.Marca) FROM
	dbo.pArhPersSal AS a LEFT OUTER JOIN
    pPerson c on a.Marca = c.marca AND a.Cod_Unit = c.Cod_Unit
	where a.anul=@an


	select @totBC=SUM(ROUND(a.VenitBC, 0)),@totI=SUM(a.Impozit) FROM
	dbo.pArhPersSal AS a INNER JOIN
             dbo.pArhExtensii AS b ON a.Marca = b.marca AND a.Luna = b.Luna AND a.Anul = b.Anul AND a.Cod_Unit = b.Cod_Unit  JOIN
             pPerson c on a.Marca = c.marca AND a.Cod_Unit = c.Cod_Unit
	where a.anul=@an


    SELECT   MAX(a.Nume_Pren) Nume_Pren,c.CNP,a.Anul,CASE WHEN a.TipContr=0 THEN 1 ELSE 2 END TipContr, SUM(a.CastigBrut) AS VenitBrutAnual,SUM(a.DedPersBz) AS DeduceriPersonale,
	SUM(a.DedSupli) AS Altededuceri,SUM(b.CotSind) AS CotSind, SUM(b.AlteDedEf) AS AlteDedEf,
	SUM(ROUND(a.VenitBC, 0)) AS VenitBC,SUM(a.Impozit) AS Impozit
	--TOP (100) PERCENT a.Marca, CASE WHEN a.Luna IS NULL THEN 'TOTAL' ELSE CASE WHEN LEN(LTRIM(RTRIM(CAST(a.Luna AS varchar(2))))) 
	--                      = 1 THEN '0' + LTRIM(RTRIM(CAST(a.Luna AS varchar(2)))) ELSE LTRIM(RTRIM(CAST(a.Luna AS varchar(2)))) END END AS Luna, SUM(a.CastigBrut) AS Brut, SUM(a.DedPersBz) AS DedPersBz, 
	--                      SUM(a.DedSupli) AS DedSupli, SUM(ROUND(a.VenitBC, 0)) AS VenitBC, SUM(a.Impozit) AS Impozit, SUM(a.NrPrsIntr) AS NrPrsIntr, SUM(b.CotSind) AS CotSind, SUM(b.AlteDedEf) AS AlteDedEf
	FROM         dbo.pArhPersSal AS a INNER JOIN
             dbo.pArhExtensii AS b ON a.Marca = b.marca AND a.Luna = b.Luna AND a.Anul = b.Anul AND a.Cod_Unit = b.Cod_Unit  JOIN
             pPerson c on a.Marca = c.marca AND a.Cod_Unit = c.Cod_Unit  
	where a.anul=@an
	GROUP BY c.CNP, a.Anul,CASE WHEN a.TipContr=0 THEN 1 ELSE 2 END
	ORDER BY c.CNP, a.Anul,CASE WHEN a.TipContr=0 THEN 1 ELSE 2 END

END